<?php
namespace app\index\controller;

use app\index\model\User;
use think\Controller;
use think\Db;
use think\Loader;
use think\Request;
use PHPExcel;
use PHPExcel_IOFactory;
use PHPExcel_Cell;

class index extends Controller
{
	public function index() {
        $user = new User();
        $userlist = $user->select();
        $this->assign('userlist', $userlist);
        return view('index');
	}

	public function create() {
        Db::startTrans();
        if (request()->file('file') == null) {
            $this->redirect('index');
        }
         vendor("PHPExcel.PHPExcel.IOFactory");
        $request = \think\Request::instance();
        $excel = request()->file('file')->getInfo();//excel为file中的name
        $objPHPExcel = \PHPExcel_IOFactory::load($excel['tmp_name']);//读取上传的文件
        $arrExcel = $objPHPExcel->getSheet(0)->toArray();//获取其中的数据
        $word = $arrExcel[0];
        array_shift($arrExcel);

        $data = array();
//        $num = count($arrExcel) -1;
         foreach ($arrExcel as $key => $value) {
             if (!empty($value[47]) || !empty($value[48])) {
                 $data[$key]["id"] = $value[0];
                 $data[$key]["class"] = $value[1];
                 $data[$key]["name"] = $value[2];
                 !empty($value[47]) && $data[$key]["av"] = $value[47];
                 !empty($value[48]) && $data[$key]["aw"] = $value[48];
             }
         }
        $user = new User();
        $userlist = $user->saveAll($data);
        if ($userlist) {
            Db::commit();
            $this->success('导入成功', 'index');
        } else {
            Db::rollback();
        }
    }

    //导出excel
    public function outexcel(){
        //导出
        $path = dirname(__FILE__); //找到当前脚本所在路径
        vendor("PHPExcel.PHPExcel.PHPExcel");
        vendor("PHPExcel.PHPExcel.Writer.IWriter");
        vendor("PHPExcel.PHPExcel.Writer.Abstract");
        vendor("PHPExcel.PHPExcel.Writer.Excel5");
        vendor("PHPExcel.PHPExcel.Writer.Excel2007");
        vendor("PHPExcel.PHPExcel.IOFactory");
        $objPHPExcel = new \PHPExcel();
        $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);


        // 实例化完了之后就先把数据库里面的数据查出来
        $sql = Db::table('user')->select();

        // 设置表头信息
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', '总序')
            ->setCellValue('B1', '先读班级')
            ->setCellValue('C1', '学生姓名')
            ->setCellValue('D1', '常规选修课')
            ->setCellValue('E1', '特长选修课');

        /*--------------开始从数据库提取信息插入Excel表中------------------*/

        $i=2;  //定义一个i变量，目的是在循环输出数据是控制行数
        $count = count($sql);  //计算有多少条数据
        for ($i = 2; $i <= $count+1; $i++) {
            $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $sql[$i-2]['id']);
            $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $sql[$i-2]['class']);
            $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $sql[$i-2]['name']);
            $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, $sql[$i-2]['av']);
            $objPHPExcel->getActiveSheet()->setCellValue('E' . $i, $sql[$i-2]['aw']);
        }
//        halt($objPHPExcel);
        /*--------------下面是设置其他信息------------------*/

        $objPHPExcel->getActiveSheet()->setTitle('companyInformation');      //设置sheet的名称
        $objPHPExcel->setActiveSheetIndex(0);                   //设置sheet的起始位置
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');   //通过PHPExcel_IOFactory的写函数将上面数据写出来

        $PHPWriter = \PHPExcel_IOFactory::createWriter( $objPHPExcel,"Excel2007");

        header('Content-Disposition: attachment;filename="学生成绩.xlsx"');
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

        $PHPWriter->save("php://output"); //表示在$path路径下面生成demo.xlsx文件



    }
}